/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package UTS.model;

import java.sql.Statement;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;
/**
 *
 * @author Vincentius HMK
 */
public class Sekretariat {
public static ArrayList<Surat> DAFTAR_SURAT = new ArrayList<Surat>();


public Sekretariat(){
}

public static void tambahDaftarSurat(Surat surat) {
        DAFTAR_SURAT.add(surat);
    }

public static String bacaDaftarSurat() {
        if (DAFTAR_SURAT.isEmpty()) {
            return "kosong";
        } else {
            String hasil = new String();
            for (int i = 0; i < DAFTAR_SURAT.size(); i++) {
                hasil += DAFTAR_SURAT.get(i).getNomor() + "\n";
            }
            return hasil;
        }
    }

    public static void simpanFileSurat() throws IOException {
        FileOutputStream outStream = null;
        if (DAFTAR_SURAT.isEmpty()) {
            JOptionPane.showMessageDialog(null, "Daftar Surat Kosong");
        } else {
            File outFile = new File("surat.dat");
            outStream = new FileOutputStream(outFile);
            ObjectOutputStream outObjectStream = new ObjectOutputStream(outStream);
            outObjectStream.writeObject(DAFTAR_SURAT);
            outStream.close();
        }
    }

    public static void bacaFileSurat() throws FileNotFoundException, IOException, ClassNotFoundException {
        FileInputStream inStream = null;
        File inFile = new File("surat.dat");
        inStream = new FileInputStream(inFile);
        ObjectInputStream inObjectStream = new ObjectInputStream(inStream);
        DAFTAR_SURAT = (ArrayList<Surat>) inObjectStream.readObject();
    }

    public static void tambahTabelSurat(Surat surat) throws SQLException{
////        Connection conn = OraConnection.open("jdbc:oracle:thin:@172.23.9.185:1521:orcl","MHS085314016", "MHS085314016");
//        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:XE","SYSTEM", "MHS085314016");
//        Statement statement = conn.createStatement();
//        String sql = "INSERT INTO SURAT(NOMOR, PERIHAL, ASAL, PENERIMA, LETAK, TANGGALDITERIMA) values " +
//                "('"+surat.getNomor() + "','" + surat.getPerihal()+"','" +surat.getAsal()+"'," +
//                "'" + surat.getPenerima() +"','" + surat.getLetak()+"'," +
//                "'" + surat.getTanggalDiterima()+"')";
//        statement.executeUpdate(sql);
//        conn.close();

//         PreparedStatement ps = OraConnection.open("jdbc:oracle:thin:@172.23.9.185:1521:orcl","MHS085314016", "MHS085314016").prepareStatement("INSERT INTO SURAT(nomor, perihal, asal, penerima, letak, tanggalDiterima, status) VALUES (?,?,?,?,?,?,?)");
         PreparedStatement ps = OraConnection.open("jdbc:oracle:thin:@localhost:1521:XE","SYSTEM", "MHS085314016").prepareStatement("INSERT INTO SURAT(nomor, perihal, asal, penerima, letak, tanggalDiterima, status) VALUES (?,?,?,?,?,?,?)");
        ps.setString(1, surat.getNomor());
        ps.setString(2, surat.getPerihal());
        ps.setString(3, surat.getAsal());
        ps.setString(4, surat.getPenerima());
        ps.setString(5, surat.getLetak());
        ps.setDate(6, new java.sql.Date(surat.getTanggalDiterima().getTime()));
        ps.setString(7, surat.getStatusSurat());

        ps.executeUpdate();

    }

    public static void simpanTabelSurat() throws SQLException {
        if (DAFTAR_SURAT.isEmpty()) {
            JOptionPane.showMessageDialog(null, "Daftar Surat Kosong");
        } else {
//            Connection conn = OraConnection.open("jdbc:oracle:thin:@172.23.9.185:1521:orcl","MHS085314016", "MHS085314016");
            Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "MHS085314016");
            Statement statement = conn.createStatement();
            for (int i = 0; i < DAFTAR_SURAT.size(); i++) {
                String sql = "INSERT INTO SURAT (NOMOR, PERIHAL, ASAL, PENERIMA, LETAK, TANGGALDITERIMA, STATUS) values ('" +
                        DAFTAR_SURAT.get(i).getNomor() + "','" +
                        DAFTAR_SURAT.get(i).getPerihal() + "','" +
                        DAFTAR_SURAT.get(i).getAsal() + "','" +
                        DAFTAR_SURAT.get(i).getPenerima() + "','" +
                        DAFTAR_SURAT.get(i).getLetak() + "','" +
                        DAFTAR_SURAT.get(i).getTanggalDiterima()+ "','" +
                        DAFTAR_SURAT.get(i).getLetak() + "')";
                statement.executeUpdate(sql);
            }
            conn.close();
        }
    }

    public static void bacaTabelSurat() throws SQLException, Exception {
        DAFTAR_SURAT = new ArrayList<Surat>();
//     Connection conn = OraConnection.open("jdbc:oracle:thin:@172.23.9.185:1521:orcl","MHS085314016", "MHS085314016");
        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "MHS085314016");
        Statement statement = conn.createStatement();
        String sql = "select * from surat";
        ResultSet result = statement.executeQuery(sql);
        Surat temp = new Surat();
        temp.setNomor(result.getString("nomor"));
        temp.setPerihal(result.getString("perihal"));
        temp.setAsal(result.getString("asal"));
        temp.setPenerima(result.getString("penerima"));
        temp.setLetak(result.getString("letak"));
        temp.setTanggalDiterima(result.getDate("tanggalDiterima"));
        temp.setStatusSurat(result.getString("status"));
        DAFTAR_SURAT.add(temp);
        conn.close();
    }
}
